sqlsrv_num_rows

    (No version information available, might only be in Git)

    sqlsrv_num_rows — Retrieves the number of rows in a result set

    Description

    sqlsrv_num_rows(resource $stmt): mixed

    Retrieves the number of rows in a result set. This function requires that the statement resource be created with a static or keyset cursor. For more information, see sqlsrv_query(), sqlsrv_prepare(), or » Specifying a Cursor Type and Selecting Rows in the Microsoft SQLSRV documentation.

    Parameters

    stmt

    The statement for which the row count is returned. The statement resource must be created with a static or keyset cursor. For more information, see sqlsrv_query(), sqlsrv_prepare(), or » Specifying a Cursor Type and Selecting Rows in the Microsoft SQLSRV documentation.

    Return Values

    Returns the number of rows retrieved on success and false if an error occurred. If a forward cursor (the default) or dynamic cursor is used, false is returned.

    Examples

    Example #1 sqlsrv_num_rows() example

    <?php
    $server     = "serverName\sqlexpress";
    $connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
    $conn = sqlsrv_connect( $server, $connectionInfo );

    $sql = "SELECT * FROM Table_1";
    $params = array();
    $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $stmt = sqlsrv_query( $conn, $sql , $params, $options );

    $row_count = sqlsrv_num_rows( $stmt );

    if (    $row_count === false)
    echo     "Error in retrieveing row count.";
    else
    echo     $row_count;
    ?>

    See Also

    Improve This Page

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 2 notes

    up
    9
    wazz3r at gmail dot com
    11 years ago
    Try to avoid using this function if you need good performance. Specifying "Scrollable" in the options will make you queries take ages to run. If your result contains less than 5000 rows (might vary on different hardware) its faster to not use "Scrollable" and loop over them in php instead.

    If you need to check if a result contains rows use "sqlsrv_has_rows()", this function works without "Scrollable". After removing all my "Scrollable" queries, my page loadtime went from 900ms to 60ms.

    To demonstrate, here is a query that returns 100 rows:
    <?php
    for($i = 0; $i < 100; $i++) {
    $q = "SELECT sku,name FROM product WHERE visible = 1";
    $result = sqlsrv_query($db,$q,array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

    while(    $row = sqlsrv_fetch_array($result)) {}
    }
    ?>
    This takes about 10s! Thats 10 qps..

    Now if we remove "Scrollable":
    <?php
    for($i = 0; $i < 100; $i++) {
    $q = "SELECT sku,name FROM product WHERE visible = 1";
    $result = sqlsrv_query($db,$q);

    while(    $row = sqlsrv_fetch_array($result)) {}
    }
    ?>
    This will run in 300ms, about 334 qps!
    up
    5
    smhahmadi
    11 years ago
    Note that when migrating your MS SQL Server PHP Driver from MSSQL to SQLSRV, if you have used mssql_num_rows, replacing them with sqlsrv_num_rows and replacing mssql_query($query, $mssql_link) with sqlsrv_query($sqlsrv_link, $query) calls will make your sqlsrv_num_rows calls fail. In order to avoid that, you should specify either static, keyset or buffered cursors (buffered cursor has been available since SQLSRV 3.0) when calling sqlsrv_query. For example,
    <?php
    mssql_query    ($query, $mssql_link);
    // is equivalent to
    sqlsrv_query($sqlsrv_link, $query, array(), array('Scrollable' => 'buffered'));
    ?>
    Using the buffered cursor is "more equivalent" than using the static or keyset cursors to simple mssql_query calls, since it caches the entire result set in client memory.
    ＋add a note
    To Top